﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace ATM.DBlayer
{
   public class AccountDB


    {

       //Database connection information
        public string myconnection = "datasource=localhost;port=3306;username=root;password=1234";
        MySqlConnection myconn = new MySqlConnection();

        public static DataGridView data = new DataGridView();
        public int count = 0;
        public string face;
        public float num, num2, result, senderBalance, reciverBalance;//3
        public int chekId = 0, chekBalance = 0 ,chekAccountNu = 0, balanceID;

       // get the transaction date and time
        DateTime transactionDate = DateTime.Now;

        public void balance (string acco)
        {
            try
            {
                string myconnection = "datasource=localhost;port=3306;username=root;password=1234";
                MySqlConnection myconn = new MySqlConnection(myconnection);
                MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM atm.atm_customers where Account_Number='" + acco + "';", myconn);
                MySqlDataReader myreader;

                myconn.Open();
                myreader = SelectCommand.ExecuteReader();
                while (myreader.Read())
                {
                    chekBalance = int.Parse((myreader["balance"].ToString()));
                }

                myconn.Close();
                // return chekBalance;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void balanceByID(string IDD)
        {
            try
            {
                string myconnection = "datasource=localhost;port=3306;username=root;password=1234";
                MySqlConnection myconn = new MySqlConnection(myconnection);
                MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM atm.atm_customers where ID='" + IDD + "';", myconn);
                MySqlDataReader myreader;

                myconn.Open();
                myreader = SelectCommand.ExecuteReader();
                while (myreader.Read())
                {
                    balanceID = int.Parse((myreader["balance"].ToString()));
                }

                myconn.Close();
                // return chekBalance;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        public void ID(string acco)
        {
            try
            {
                string myconnection = "datasource=localhost;port=3306;username=root;password=1234";
                MySqlConnection myconn = new MySqlConnection(myconnection);
                MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM atm.atm_customers where Account_Number='" + acco + "';", myconn);
                MySqlDataReader myreader;

                myconn.Open();
                myreader = SelectCommand.ExecuteReader();
                while (myreader.Read())
                {
                    chekId = int.Parse((myreader["ID"].ToString()));
                }

                myconn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void AcountNumber(string id)
        {
            try
            {
                string myconnection = "datasource=localhost;port=3306;username=root;password=1234";
                MySqlConnection myconn = new MySqlConnection(myconnection);
                MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM atm.atm_customers where ID='" + id + "';", myconn);
                MySqlDataReader myreader;

                myconn.Open();
                myreader = SelectCommand.ExecuteReader();
                while (myreader.Read())
                {
                    chekAccountNu = int.Parse((myreader["Account_Number"].ToString()));
                }

                myconn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public void balnceChange(string Acc, int balan)
        {
            float fl = (float)balan;
            string myconnection = "datasource=localhost;port=3306;username=root;password=1234";
            MySqlConnection myconn = new MySqlConnection(myconnection);

            MySqlCommand SelectCommand = new MySqlCommand("update atm.atm_customers set balance='" + fl + "' where Account_Number='" + Acc + "';", myconn);
            MySqlDataReader myreader;

            myconn.Open();
            myreader = SelectCommand.ExecuteReader();



            myconn.Close();




             


        }


        public void logoing(string data)
        {

            try
            {
                string myconnection = "datasource=localhost;port=3306;username=root;password=1234";
                MySqlConnection myconn = new MySqlConnection(myconnection);
                MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM atm.atm_customers where Account_Number='" + data + "';", myconn);
                MySqlDataReader myreader;/// kol dah el rabt bel database

                myconn.Open();
                myreader = SelectCommand.ExecuteReader();

                while (myreader.Read())// law 3amal fe3lan connect w2ara el account num sa7 5aly el count b 1
                {
                    count =  1;
                }

                myconn.Close();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }



        public void interfaccee(string inte)
        {
            string myconnection = "datasource=localhost;port=3306;username=root;password=1234";
            MySqlConnection myconn = new MySqlConnection(myconnection);

            MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM atm.atm_customers where Account_Number='" + inte + "';", myconn);// hagep kol elly fe el data base elly el accnum bta3o kaza elly hwa ma7tot fe x

            try
            {
                MySqlDataAdapter sda = new MySqlDataAdapter();// de elly bt3mel update lel table
                sda.SelectCommand = SelectCommand;// de elly bt2olak ha3mel update feen wbema en ana 3amel select * fhay3mel update fe ay  7eta fe elgadwal ana 2ayelo 3leha e3mel feh ta3'yeer
                DataTable dbdataset = new DataTable();// ba5od data mn el database server wba3melha store fe memory mo2aqata
                sda.Fill(dbdataset);// ba7ot el update elly ana 3amalto fe elgadwal elly ma7tot mo2aqatan fe el memory
                BindingSource bsource = new BindingSource();// hna barbot el memory elmo2aqta bel database el2aslya 
                bsource.DataSource = dbdataset;//de el function elly btorbot
                data.DataSource = bsource; // bya5od el update mn el memory elmo2aqat wby7otaha fe mkanha fe eldatabase
                sda.Update(dbdataset);//by3melha isnsert fe eldatabase wkda 5alas elupdate 5alas w5ado mn el memory elmo2aqata w7ataha fe el database belupdate
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }




        public float GetBalance(int accountNumber) {
            MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM atm.atm_customers where Account_Number='" + accountNumber + "';", myconn);
            myconn.Open();
            try
            {
                MySqlDataReader myreader = SelectCommand.ExecuteReader();
                while (myreader.Read())
                {

                    num = myreader.GetFloat("balance");// retrieve the balance from the DB .
                }
                myconn.Close();
            }
            catch
            {
                throw;
            }

            return num;
        }


        public void RecordDeposit(string accountNumber, float balance, int amount)
        {

            // recording the transaction in the Database 

            MySqlCommand InsertCommand = new MySqlCommand("INSERT INTO atm.History VALUES ('" + accountNumber + "' , 2 , 'DEPOSIT' , '" + amount + "', '" + balance + "' , '" + transactionDate + "' );", myconn);
            myconn.Open();


            try
            {

                MySqlDataReader myreader = InsertCommand.ExecuteReader();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            myconn.Close();



        }

        public void RecordWithdraw(string accountNumber, float balance, int amount)
        {

            // recording the transaction in the Database 
            MySqlCommand InsertCommand = new MySqlCommand("INSERT INTO atm.History VALUES ('" + accountNumber + "' , 1 , 'WITHDRAW' , '" + amount + "', '" + balance + "' , '" + transactionDate + "' );", myconn);
            myconn.Open();


            try
            {

                MySqlDataReader myreader = InsertCommand.ExecuteReader();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            myconn.Close();

        }







        public void RecordTransfer(string accountNumber, float balance, int amount)
        {

            // recording the transaction in the Database 
            MySqlCommand InsertCommand = new MySqlCommand("INSERT INTO atm.History VALUES ('" + accountNumber + "' , 3 , 'TRANSFER' , '" + amount + "', '" + balance + "' , '" + transactionDate + "' );", myconn);
            myconn.Open();

            try
            {

                MySqlDataReader myreader = InsertCommand.ExecuteReader();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            myconn.Close();
        }


        public void RecordPaybill(string accountNumber, float balance, int amount)
        {

            // recording the transaction in the Database 
            MySqlCommand InsertCommand = new MySqlCommand("INSERT INTO atm.History VALUES ('" + accountNumber + "' , 4 , 'PAYBILL' , '" + amount + "', '" + balance + "' , '" + transactionDate + "' );", myconn);
            myconn.Open();

            try
            {

                MySqlDataReader myreader = InsertCommand.ExecuteReader();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            myconn.Close();
        }

    }
}


                
               
                    
              
           

         



                   

        
       
    
